package top.zhangjianyong.utils.file;

import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import top.zhangjianyong.utils.object.BaseObject;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

/**
 * @program: topzhangjianyongutils
 * @description: Excel解析工具类
 * @author: 张建勇
 * @create: 2019-03-15 10:38
 **/
public class ExcelUtil {

    /**
     * 初始化
     *
     * @param filePath
     * @return
     */
    private static Workbook getReadWorkBookType(String filePath) {
        //xls-2003, xlsx-2007
        FileInputStream is = null;
        try {
            is = new FileInputStream(filePath);
            if (filePath.toLowerCase().endsWith("xlsx")) {
                return new XSSFWorkbook(is);
            } else if (filePath.toLowerCase().endsWith("xls")) {
                return new HSSFWorkbook(is);
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            IOUtils.closeQuietly(is);
        }
        return null;
    }

    /**
     * 读取excel
     *
     * @param sourceFilePath
     * @param sheetNum
     * @param columnNum      列数
     * @return
     */
    public static List<List<String>> readExcel(String sourceFilePath, int sheetNum, int columnNum) {
        Workbook workbook = null;

        try {
            workbook = getReadWorkBookType(sourceFilePath);
            List<List<String>> contents = new ArrayList<List<String>>();

            //获取第一个sheet
            Sheet sheet = workbook.getSheetAt(sheetNum);
            //第0行是表名，忽略，从第二行开始读取

            for (int rowNum = 1, size = sheet.getLastRowNum(); rowNum <= size; rowNum++) {
                Row row = sheet.getRow(rowNum);
                List<String> list = new ArrayList<String>();
                for (int i = 0; i < columnNum; i++) {
                    Cell cell = row.getCell(i);
                    list.add(getCellStringVal(cell).trim());
                }
                contents.add(list);
            }
            return contents;
        } finally {
            IOUtils.closeQuietly(workbook);
        }
    }

    private static String getCellStringVal(Cell cell) {
        CellType cellType = cell.getCellTypeEnum();
        switch (cellType) {
            case NUMERIC:
                return cell.getStringCellValue();
            case STRING:
                return cell.getStringCellValue();
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            case BLANK:
                return "";
            case ERROR:
                return String.valueOf(cell.getErrorCellValue());
            default:
                return "";
        }
    }

    private static Workbook getWriteWorkBoolType(String filePath) {
        if (filePath.toLowerCase().endsWith("xlsx")) {
            return new XSSFWorkbook();
        } else if (filePath.toLowerCase().endsWith("xls")) {
            return new HSSFWorkbook();
        }
        return null;
    }

    /**
     * 写入数据到exccel
     *
     * @param targetFilePath
     * @param contents
     */
    public static void writeExcel(String targetFilePath, String sheetName, List<? extends BaseObject> contents) throws NoSuchFieldException, IllegalAccessException {
        if (CollectionUtils.isEmpty(contents)) {
            return;
        }
        Workbook workbook = null;
        FileOutputStream fos = null;
        workbook = getWriteWorkBoolType(targetFilePath);

        //列名数组
        List<String> columnNames = contents.get(0).AllFieldNam();

        //创建sheet
        Sheet sheet = workbook.createSheet(sheetName);
        //在sheet第一行写出表单的各个字段名
        Row titleRow = sheet.createRow(0);
        for (int i = 0, size = columnNames.size(); i < size; i++) {
            titleRow.createCell(i).setCellValue(columnNames.get(i));
        }

        //每行的单元格一次写入
        Integer rowIndex = contents.size();
        for (int i = 0; i < rowIndex; i++) {
            //第1行作为表格列名，所以从第2行开始读取
            Row row = sheet.createRow(i + 1);
            for (int j = 0, size = columnNames.size(); j < size; j++) {
                //行对象
                BaseObject baseObject = contents.get(i);
                //字段名
                String columnName = columnNames.get(j);
                //字段值
                Object object = baseObject.getAttributesByName(columnName);
                //设置单元格值
                row.createCell(j).setCellValue(String.valueOf(object));
            }
        }
        //写入到文件流中
        try {
            fos = new FileOutputStream(targetFilePath);
            workbook.write(fos);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            IOUtils.closeQuietly(workbook);
        }
    }

    public static void main(String[] args) {
        String fileName = "D:\\test.xlsx";
        System.out.println("测试");
        List<List<String>> lists = readExcel(fileName, 0, 2);

        System.out.println(lists);
    }

}
